package com.eascs.web.o2o.scheduler.service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StopWatch;
import com.eascs.web.o2o.dc.dao.WorkFlowDao;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.entity.HrEmployee;
import com.eascs.web.o2o.scheduler.entity.HrUnit;
import com.eascs.web.o2o.scheduler.entity.O2oCustomer;
import com.eascs.web.o2o.scheduler.entity.O2oPlatformRegionalEntity;
import com.eascs.web.o2o.scheduler.entity.ProcessMonitorReport;
import com.eascs.web.o2o.scheduler.entity.PubDatadictionary;
import com.eascs.web.o2o.scheduler.entity.PubDditem;
import com.eascs.web.o2o.scheduler.entity.ReportPingtaiDelivery;
import com.eascs.web.o2o.scheduler.history.BaseService;
import com.eascs.web.o2o.scheduler.util.Constant;
import com.eascs.workflow.common.entity.sys.PlatformRegional;
import com.eascs.workflow.common.service.sys.SysOrgService;
import com.eascs.workflow.common.vo.ModelResult;

@Service
public class DataCenterService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	BaseService baseService;
	@Inject
	ServiceDao serviceDao;
	@Inject
	BaseDao baseDao;
	@Inject
	WorkFlowDao workFlowDao;
	@Inject
	ServiceService serviceService;
	
	
	


	// 省区流程监控表数据入库
	public void processMonitorReport() {
		String deleteSql = "DELETE FROM process_monitor_report";
		logger.info("删除省区流程监控表数据开始=============");
		baseDao.deleteEntity(deleteSql);
		logger.info("删除省区流程监控表数据完成=============");

		String querySql = " SELECT opr.regional_name as regional_name, ol.ltd_name, ol.operator_name, olu.user_full_name, ol.loan_type, ol.wf_type, ol.create_date, ol.loana_mount, ol.ea_mount,opr.province_code,ol.ltd FROM o2o_loanapply ol JOIN o2o_loan_user olu ON ol.loanapply_no = olu.loanapply_no AND olu.spouse_id IS NULL AND olu.sponsor_type IS NULL JOIN o2o_platform_regional opr ON opr.platform_id = ol.ltd WHERE ol.wf_type IN ( 'NEW', 'Z', 'ZF', 'N', 'NF', 'S' ) AND ol.islock = 'N' ORDER BY opr.regional_name, ol.ltd_name, ol.operator_name, ol.create_date ";
		int pageIndex = 0;
		int pageSize = 100;
		while (pageIndex >= 0) {
			logger.info("省区流程监控表数据入库任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<ProcessMonitorReport> sqlPageQuery = new SqlPageQuery<>(ProcessMonitorReport.class, pageSize,
					pageIndex, querySql);
			PageResult<ProcessMonitorReport> results = serviceDao.findDataWithPageForService(sqlPageQuery);

			List<ProcessMonitorReport> list = results.getData();
			for (ProcessMonitorReport process : list) {
				ProcessMonitorReport entity = new ProcessMonitorReport();
				entity.setRegionalName(process.getRegionalName());
				entity.setLtdName(process.getLtdName());
				entity.setOperatorName(process.getOperatorName());
				entity.setUserFullName(process.getUserFullName());
				entity.setLoanType(process.getLoanType());
				entity.setWfType(process.getWfType());
				entity.setCreateDate(process.getCreateDate());
				entity.setLoanaMount(process.getLoanaMount());
				entity.setEaMount(process.getEaMount());
				entity.setProvinceCode(process.getProvinceCode());
				entity.setLtd(process.getLtd());

				baseDao.saveEntity(entity);
			}
			pageIndex++;
			if (results.getPageTotal() <= pageIndex) {
				pageIndex = -1;
			}
		}
		logger.info("省区流程监控表数据入库任务结束=============");

	}

	
	
	  
    /**
     * @Title: saveReportPingtaiDelivery
     * @Description:保存 O2O金融业务台账——投放量统计表（按平台) 数据
     * @author 张庆奇
     * @return void
     * @throws
     * @date 2016年8月18日 下午1:24:37
     */
    public void saveReportPingtaiDelivery(){
		// 1.插入今年有投放量的平台以及省区等字段
		baseDao.deleteEntity("delete from report_pingtai_delivery");
		String sql = "sql.reportPingtaiDelivery";
       
		logger.info("插入今年有投放量的平台以及省区等字段开始......");
		try {
			baseDao.excuteSql(sql);
		} catch (Exception e) {
			logger.error("插入今年有投放量的平台以及省区等字段出现异常......");
			throw e;

		}
		logger.info("插入今年有投放量的平台以及省区等字段结束......");
		//2.更新表剩余的字段
		 String findsql="select id, province,ltd_name,loan_num_year,loan_amount_year,loan_amount_rage_year,rec_manger,rec_risk,rec_deposite from report_pingtai_delivery order by convert(ltd_name using gbk) asc" ;
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<ReportPingtaiDelivery> sqlPageQuery = new SqlPageQuery<>(ReportPingtaiDelivery.class,
					Constant.PROCESS_DATA_LIMIT, index, findsql);
			PageResult<ReportPingtaiDelivery> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				ReportPingtaiDelivery obj = pageResult.getData().get(i);
				if(StringUtils.isEmpty(obj.getLtdName()))return;
				try {
					
                   // 1.比去年同期新增金额
					BigDecimal year_amount=obj.getLoanAmountYear();// 今年投放金额
					BigDecimal last_year_year_amount=new BigDecimal(0);// 去年投放金额
					sql="SELECT IFNULL(sum(loan_amount), 0) AS loan_amount_year FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND YEAR (refund.loan_date) = YEAR ( date_sub(now(), INTERVAL 1 YEAR)) AND ltd_name =?";
					ReportPingtaiDelivery delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						last_year_year_amount=delivery.getLoanAmountYear();	
					}
					obj.setAddLastYear(year_amount.subtract(last_year_year_amount));//比去年同期新增金额
				   // 2.本省排名
					
                   // 3.查询该平台下的本年新客户投放笔数，和金额
					sql="SELECT count(1) AS loan_num_new_cus_year, IFNULL(sum(refund.loan_amount),0) AS loan_amount_new_cus_year FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND YEAR (refund.loan_date) = YEAR (now()) AND ltd_name =? AND is_renew_loan='N' ";
					delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						obj.setLoanNumNewCusYear(delivery.getLoanNumNewCusYear());//本年新客户投放笔数
						obj.setLoanAmountNewCusYear(delivery.getLoanAmountNewCusYear());//本年新客户投放金额
						
					}
                    
					//4.本省排名
					
				    //5.查询该平台下的本年续贷客户投放的笔数
					sql="SELECT count(1) AS loan_num_old_cus_year, IFNULL(sum(refund.loan_amount), 0) AS loan_amount_old_cus_year FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND YEAR (refund.loan_date) = YEAR (now()) AND ltd_name =? AND  is_renew_loan='Y' ";
					 delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						obj.setLoanNumOldCusYear(delivery.getLoanNumOldCusYear());//本年续贷客户投放笔数
						obj.setLoanAmountOldCusYear(delivery.getLoanAmountOldCusYear());//本年续贷客户投放金额
						
					}
					//6.本省排名
					
					//7.新客户投放量占比
					int num=obj.getLoanNumNewCusYear()+obj.getLoanNumOldCusYear();
					if (num > 0) {
						obj.setNewCusRageYear(new BigDecimal(obj.getLoanNumNewCusYear()).divide((new BigDecimal(num)),
								4, RoundingMode.HALF_UP));// 新客户投放量占比
					}
					// 8.查询该平台下的月度累计投放量笔数、金额、笔均金额
					sql = "SELECT loan_num_month, loan_amount_month, loan_amount_month / loan_num_month AS loan_amount_rage_month FROM ( SELECT count(1) AS loan_num_month, sum(refund.loan_amount) AS loan_amount_month FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND DATE_FORMAT(refund.loan_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND ltd_name=? ) t";
					delivery = (ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class,obj.getLtdName());
					if (delivery != null) {
						obj.setLoanNumMonth(delivery.getLoanNumMonth());// 本月投放笔数
						obj.setLoanAmountMonth(delivery.getLoanAmountMonth());// 本月投放金额
						if (delivery.getLoanNumMonth() > 0) {
							obj.setLoanAmountRageMonth(delivery.getLoanAmountMonth()
									.divide(new BigDecimal(delivery.getLoanNumMonth()), 4, RoundingMode.HALF_UP));// 月度累计投放比均金额
						}
					}
					//9.比上月新增金额
					BigDecimal month_amount=obj.getLoanAmountMonth();// 本月投放金额
					BigDecimal last_month_amount=new BigDecimal(0);// 上个月投放金额
					sql="SELECT IFNULL(sum(loan_amount), 0) AS loan_amount_month FROM report_refund_add refund WHERE  refund.loan_type = 'BK' AND PERIOD_DIFF( date_format(now(), '%Y%m'), date_format(refund.loan_date, '%Y%m')) = 1 AND ltd_name =?";
					delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						last_month_amount=delivery.getLoanAmountMonth();	
					}
					obj.setAddLastMonth(month_amount.subtract(last_month_amount));//比上月新增金额
					//10.本省排名
					
					//11. 查询该平台下的本月新客户投放笔数，和金额
					sql="SELECT count(1) AS loan_num_new_cus_month, IFNULL(sum(refund.loan_amount), 0) AS loan_amount_new_cus_month FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND DATE_FORMAT(refund.loan_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND ltd_name =? AND id_num IN ( SELECT id_num FROM report_refund_add refund  GROUP BY refund.id_num HAVING COUNT(1) = 1 )";
					delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						obj.setLoanNumNewCusMonth(delivery.getLoanNumNewCusMonth());//本月新客户投放笔数
						obj.setLoanAmountNewCusMonth(delivery.getLoanAmountNewCusMonth());//本月新客户投放金额
					}
					//12. 本省排名
					
					//13.查询该平台下的本月续贷客户投放的笔数
					 sql="SELECT count(1) AS loan_num_old_cus_month, IFNULL(sum(refund.loan_amount), 0) AS loan_amount_old_cus_month FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND DATE_FORMAT(refund.loan_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND ltd_name =? AND id_num IN ( SELECT id_num FROM report_refund_add refund  GROUP BY refund.id_num HAVING COUNT(1) > 1 )";
					 delivery=	(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());
					if(delivery!=null){
						obj.setLoanNumOldCusMonth(delivery.getLoanNumOldCusMonth());//本月续贷客户投放笔数
						obj.setLoanAmountOldCusMonth(delivery.getLoanAmountOldCusMonth());//本月续贷客户投放金额
					}
					//14.本省排名
				
					//15.报告期T-1日放款笔数和金额
					sql="SELECT count(1) AS day_add_num, IFNULL(sum(loan_amount), 0) AS day_add_amount FROM report_refund_add refund WHERE refund.loan_type = 'BK' AND TO_DAYS(NOW()) - TO_DAYS(refund.loan_date) = 1 AND ltd_name=? ";
					delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, obj.getLtdName());					 
					if(delivery!=null){
						obj.setDayAddNum(delivery.getDayAddNum());//t-1 日投放笔数
						obj.setDayAddAmount(delivery.getDayAddAmount());//t-1投放金额
					}
					//16.操作时间
					obj.setOperTime(new Date());
				   // 17 .更新report_pingtai_delivery 表数据
					logger.info("更新report_pingtai_delivery 表数据开始......");
					baseDao.updateEntity(obj);
					logger.info("更新report_pingtai_delivery 表数据结束......");
				} catch (Exception e) {
					logger.error("数据库异常", e);
					logger.info("主键:{}",obj.getId());
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		// 更新排名
		List<ReportPingtaiDelivery> pingtaiDeliveryList=(List<ReportPingtaiDelivery>) baseDao.findEntitysForMysql("select * from report_pingtai_delivery", ReportPingtaiDelivery.class);
		for (ReportPingtaiDelivery re : pingtaiDeliveryList) {
			// 1.本年累计放款排名
			sql="SELECT rownum AS rank_year FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT add_last_year AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			ReportPingtaiDelivery	delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_year =? where id=?", delivery.getRankYear(),re.getId());
			}
			//2.本年新客户投放排名
			sql="SELECT rownum AS rank_new_cus_year FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT loan_amount_new_cus_year AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_new_cus_year =? where id=?", delivery.getRankNewCusYear(),re.getId());
			}
			//3.本年续贷客户投放排名
			sql="SELECT rownum AS rank_old_cus_year FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT loan_amount_old_cus_year AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_old_cus_year =? where id=?", delivery.getRankOldCusYear(),re.getId());
			}
			//4.月度累计投放量排名
			sql="SELECT rownum AS rank_month FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT add_last_month AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_month =? where id=?", delivery.getRankMonth(),re.getId());
			}
			//5.本月新客户投放排名
			sql="SELECT rownum AS rank_new_cus_month FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT loan_amount_new_cus_month AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_new_cus_month =? where id=?", delivery.getRankNewCusMonth(),re.getId());
			}
			//6.本月续贷客户投放排名
			sql="SELECT rownum AS rank_old_cus_month FROM ( SELECT ltd_name, CASE WHEN @rowtotal = obj.amount THEN @rownum WHEN @rowtotal := obj.amount THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT loan_amount_old_cus_month AS amount, ltd_name FROM report_pingtai_delivery WHERE province =? ORDER BY amount DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) t WHERE ltd_name =?";
			delivery=(ReportPingtaiDelivery) baseDao.findEntityForMysql(sql, ReportPingtaiDelivery.class, re.getProvince(),re.getLtdName());					 
			if(delivery!=null){
				baseDao.excuteSql("update report_pingtai_delivery set rank_old_cus_month =? where id=?", delivery.getRankOldCusMonth(),re.getId());
			}
		}

	}
    

}
